package icom.acm.manager;

import icom.acm.bean.T_U_BASEINFO;
import icom.acm.util.DBConnect;

import java.sql.Statement;
import java.sql.Timestamp;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

public class T_U_BaseInfoMgr {
	
	public boolean insertOrUpdate(final T_U_BASEINFO userInfo){
		String sql="insert into T_U_BASEINFO(U_ID, U_ADDRESS," +
				" U_SEX, U_BIRTHDAY, U_BLOGURL, U_QQ, U_MSN, U_STATEMENT) " +
				"values(?, ?, ?, ?, ?, ?, ?, ?) " +
				"on DUPLICATE KEY UPDATE U_ADDRESS=?, " +
				"U_SEX=?, U_BIRTHDAY=?, U_BLOGURL=?, " +
				"U_QQ=?, U_MSN=?, U_STATEMENT=?";
		DBConnect db = new DBConnect();
		db.setAutoCommit(false);
		db.prepareStatement(sql);
		
		db.setInt(1, userInfo.getU_ID());
		db.setString(2, userInfo.getU_ADDRESS());
		db.setString(3, userInfo.getU_SEX());
		db.setTimestamp(4, new Timestamp(userInfo.getU_BIRTHDAY().getTime()));
		db.setString(5, userInfo.getU_BLOGURL());
		db.setString(6, userInfo.getU_QQ());
		db.setString(7, userInfo.getU_MSN());
		db.setString(8, userInfo.getU_STATEMENT());
		
		db.setString(9, userInfo.getU_ADDRESS());
		db.setString(10, userInfo.getU_SEX());
		db.setTimestamp(11, new Timestamp(userInfo.getU_BIRTHDAY().getTime()));
		db.setString(12, userInfo.getU_BLOGURL());
		db.setString(13, userInfo.getU_QQ());
		db.setString(14, userInfo.getU_MSN());
		db.setString(15, userInfo.getU_STATEMENT());
		try {
			db.excute();
			//flag = db.getGeneratedKeys();
			db.commit();
		} catch (MySQLIntegrityConstraintViolationException e) {
			// 数据重复
			//flag = -1;
			return false;
		} finally{
			db.setAutoCommit(true);
			db.close();
		}
		//return flag;
		return true;
	}
	public int insert(final T_U_BASEINFO userInfo){
		int flag = -1;
		String sql = "INSERT INTO T_U_BASEINFO(U_ID)" +
				" VALUES(?)";
		DBConnect db = new DBConnect();
		db.setAutoCommit(false);
		db.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);		
		db.setInt(1, userInfo.getU_ID());
		try {
			db.excute();
			flag = db.getGeneratedKeys();
			db.commit();
		} catch (MySQLIntegrityConstraintViolationException e) {
			// 数据重复
			flag = -1;
		} finally{
			db.setAutoCommit(true);
			db.close();
		}
		return flag;
	}
	
	public int update(final T_U_BASEINFO userInfo){
		int flag = -1;
		String sql = "UPDATE T_U_BASEINFO SET U_ADDRESS=?, U_SEX=?," +
				" U_BIRTHDAY=?, U_BLOGURL=?, U_QQ=?, U_MSN=?, U_STATEMENT=? " +
				"WHERE U_ID=?";
		DBConnect db = new DBConnect();
		db.setAutoCommit(false);
		db.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		db.setString(1, userInfo.getU_ADDRESS());
		db.setString(2, userInfo.getU_SEX());
		db.setTimestamp(3, new Timestamp(userInfo.getU_BIRTHDAY().getTime()));
		db.setString(4, userInfo.getU_BLOGURL());
		db.setString(5, userInfo.getU_QQ());
		db.setString(6, userInfo.getU_MSN());
		db.setString(7, userInfo.getU_STATEMENT());
		db.setInt(8, userInfo.getU_ID());
		try {
			db.excute();
			flag = db.getGeneratedKeys();
			db.commit();
		} catch (MySQLIntegrityConstraintViolationException e) {
			// 数据重复
			flag = -1;
		} finally{
			db.setAutoCommit(true);
			db.close();
		}
		return flag;
	}
	
	public T_U_BASEINFO GetUserInfo(final int uid) {
		T_U_BASEINFO userInfo = null;
		final DBConnect db = new DBConnect();
		final String sql = "SELECT *FROM T_U_BASEINFO WHERE U_ID=?";
		db.prepareStatement(sql);
		db.setInt(1, uid);
		db.excuteQuery();
		try {
			while(db.next()) {
				userInfo = new T_U_BASEINFO();
				userInfo.setU_ID(db.getInt("U_ID"));
				userInfo.setU_ADDRESS(db.getString("U_ADDRESS"));
				userInfo.setU_SEX(db.getString("U_SEX"));
				userInfo.setU_BIRTHDAY(db.getDate("U_BIRTHDAY"));
				userInfo.setU_BLOGURL(db.getString("U_BLOGURL"));
				userInfo.setU_QQ(db.getString("U_QQ"));
				userInfo.setU_MSN(db.getString("U_MSN"));
				userInfo.setU_STATEMENT(db.getString("U_STATEMENT"));
			}
		} catch (final Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			db.close();
		}
		return userInfo;
	}
}
